<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class User_profiles extends CI_Model{
    private $ERROR_DB = false;

    public function __construct() {
        parent::__construct();
        
        $conn = $this->db->db_connect();
        if($conn === false) $this->ERROR_DB = true;
        else{
//            $params = $this->get_all_user_params($this->session->userdata('uid'));
//            $this->u_menu       = $params['u_menu'];
//            $this->u_bookmarks  = $params['u_bookmarks'];
//            $this->u_params     = $params;
//
//            $this->u_general    = $this->get_u_general();

        }
    }

    /**
     * Переключает и запоминает состояние узла закрыт/открыт
     * @param type $hash_node
     * @param type $status 1 = open; 0 = close
     * @return string
     */
    public function change_node($hash_node,$status){
        $res = false;
        if($status == 1){
            $res = $this->db->query("INSERT INTO user_menu (user_id,u_hash,status) VALUES (?,?,?)",array($this->session->userdata('uid'),$hash_node,$status));
        }elseif($status == 0){
            $res = $this->db->query("DELETE FROM user_menu WHERE user_id = ? AND u_hash = ?",array($this->session->userdata('uid'),$hash_node));
        }
        return $res;
    }


   /**
     * Переключает и запоминает состояние узла ползовательских закладок: закрыт/открыт
     * @param type $hash_node
     * @param type $status 1 = open; 0 = close
     * @return string
     */
    public function change_bookmark($bookmark_id,$status){
        $res = false;
        if($status == 1){
            $res = $this->db->query("INSERT INTO user_bookmarks (user_id,bookmark_id,status) VALUES (?,?,?)",array((int)$this->session->userdata('uid'),(int)$bookmark_id,(int)$status));
        }else{
            $res = $this->db->query("DELETE FROM user_bookmarks WHERE user_id = ? AND bookmark_id = ?",array((int)$this->session->userdata('uid'),(int)$bookmark_id));
        }
        return $res;
    }

    /**
     * Получить массив (хэш/ид) открытых узлов дерева
     * @return array
     */
    public function get_u_menu(){
        $query = $this->db->query("SELECT a.u_hash, b.r_id FROM user_menu a inner join ".$this->config->item('resolv_table')." b on a.u_hash = b.r_hash  WHERE user_id = ?",array($this->session->userdata('uid')));
        $u_menu = array();
        foreach($query->result_array() as $row){
            $u_menu[$row['U_HASH']] = $row['R_ID'];
        }
        return $u_menu;
    }

   /**
     * Получить массив (ид/статус) открытых узлов ползовательских закладок
     * @return array
     */
    public function get_u_bookmarks(){
        $query = $this->db->query("select * from user_bookmarks where user_id = ?",array($this->session->userdata('uid')));
        $u_bookmarks = array();
        foreach($query->result_array() as $row){
            $u_bookmarks[$row['BOOKMARK_ID']] = $row['STATUS'];
        }
        return $u_bookmarks;
    }

    /**
     * admin part
     *
     */

    public function get_users_table_depricated(){

        $tmpl = array ('table_open' => '<table id="users-tab" class="table table-striped table-hover table-condensed">');

        $this->table->set_template($tmpl);
        $this->table->set_heading('ID', 'Логин', 'Входов','Посл.вход','Домен','Роль','Описание','Действия');
        $this->table->set_empty("");

//        $query = $this->db->query('SELECT id, user_name, logins, last_login, domain FROM users order by id');
        $query = $this->db->query('SELECT u.id, u.user_name, u.logins, u.last_login, u.domain, r.role_name, u.description, u.id as event from users u, roles r, roles_users ru '
                                    .'where u.id = ru.user_id AND r.id = ru.role_id '
                                    .'order by u.id');
        $table = $this->table->generate($query);

        return $table;
    }
    
    public function get_users_table(){ //TO_CHAR(col1, 'mm/dd/yyyy') 
        $query = $this->db->query('SELECT u.id, u.user_name, u.logins, TO_CHAR(u.last_login, \'dd.mm.yyyy\') as last_login,to_number(to_char(SYSDATE, \'J\')) - TO_NUMBER(to_char(u.last_login, \'J\')) as dtdiff, u.domain, d.name as domain_name, r.role_name, r.id as role_id, u.description '
                                    .'from users u, roles r, roles_users ru, domains d '
                                    .'where u.id = ru.user_id AND r.id = ru.role_id AND u.domain = d.value '
                                    .'order by u.id');
        
        $table = '<table id="users-tab" class="table table-striped table-hover table-condensed">';
        $table .= '<tr><th width="20">ID</th><th>Логин</th><th>Входов</th><th>Посл.вход</th><th>Домен</th><th>Роль</th><th>Описание</th><th>Действия</th></tr>';
        foreach ($query->result_array() as $row){
            //подкрашивать поле  пользователя если он долго не входил в систему
            if($row['DTDIFF'] > 365){
                $trclass = ' class="danger"';
            }elseif($row['DTDIFF'] > 180){
                $trclass = ' class="warning"';
            }elseif($row['DTDIFF'] > 90){
                $trclass = ' class="info"';
            }else{
                $trclass = "";
            }
            
            $table .= sprintf('<tr%s><td>%d</td><td>%s</td><td>%d</td><td>%s</td><td data-domainval="%s">%s</td><td data-roleid="%d">%s</td><td>%s</td><td width="50" data-userid="%d">'
                    . '<span class="badge alert-warning glyphicon glyphicon-pencil hand has-tooltip" data-toggle="modal" data-target="#modalEditUser" title="Редактировать"> </span> '
                    . '<span class="badge alert-danger glyphicon glyphicon-remove hand has-tooltip" data-toggle="modal" data-target="#modalDelUser" title="Удалить"> </span></td></tr>',
                    $trclass,$row['ID'],$row['USER_NAME'],$row['LOGINS'],$row['LAST_LOGIN'],$row['DOMAIN'],$row['DOMAIN_NAME'],$row['ROLE_ID'],$row['ROLE_NAME'],$row['DESCRIPTION'],$row['ID']);
        }
        $table .= '</table>';
        return $table;
    }
    
    


    public function add_new_user($login,$domain,$usrdescr){
        $this->db->query("insert into users (user_name,domain,description) values (?,?,?)",array(strtolower($this->db->escape_str($login)),$this->db->escape_str($domain),$this->db->escape_str($usrdescr)));
        return $this->db->affected_rows();
    }

    public function update_user($userid,$username,$domain,$roleid,$usrdescr){
        //echo $userid,$username,$domain,$roleid;
        $this->db->query("update users SET user_name = ?, domain = ?, description = ? WHERE id = ?",array(strtolower($this->db->escape_str($username)),$this->db->escape_str($domain),$this->db->escape_str($usrdescr),$this->db->escape_str($userid)));
        if($this->db->affected_rows() > 0){
            $this->db->query("update roles_users SET role_id = ? WHERE user_id = ?",array($this->db->escape_str($roleid),$this->db->escape_str($userid)));
            return $this->db->affected_rows();
        }else{
            return false;
        }
    }

    public function delete_user($userid){
//        $this->db->query("update users SET user_name = 'deleted' WHERE id = ?",array((int)$userid));
        $this->db->query("delete from users WHERE id = ?",array((int)$userid));
        $aff_user = $this->db->affected_rows();
        if($aff_user > 0){
            $this->db->query("delete from bookmarks WHERE user_id = ?",array((int)$userid));
        }
        return $aff_user;
    }

    public function clear_up_trash($userid){
        $affect = 0;
        $query_bookmarks = "delete from bookmarks where bookmarks.id IN (
            with A as (select bk.id, bk.user_id, rs.* from bookmarks bk inner join resolv_list_tmp rs on bk.type_id = rs.r_id WHERE user_id = ?),
            B as (select '%' || prm.key_uuid || '%' uuid  from roles_permission prm inner join roles_users usr on prm.role_id = usr.role_id where usr.user_id = ?)
            select A.id from A where not EXISTS (select 1 from B where A.r_path like B.uuid))";
        $this->db->query($query_bookmarks,array((int)$userid,(int)$userid));
        $affect += $this->db->affected_rows();
        
        $query_u_menu = "delete from user_menu where user_id = ?";
        $this->db->query($query_u_menu,array((int)$userid));
        $affect += $this->db->affected_rows();
        
        return $affect;
    }

    /**!!
     * Получаем всех пользователей с этой ролью
     * @param int $roleid
     * @return array 
     */
    public function get_users_by_roleid($roleid){
        $query = $this->db->query("select us.user_name user_name from roles_users rl inner join users us on rl.user_id = us.id
                            where rl.role_id = ? order by us.id",array((int)$roleid));
        $users = array();
        foreach ($query->result_array() as $row){
            $users[] = $row['USER_NAME'];
        }
        return $users;
    }

    
    /**
     * Получить список всех доступных доменов
     * @return type
     */
    public function get_domains(){
        $query = $this->db->query("select * from domains order by sort, name");
        $domains = array();
        foreach ($query->result_array() as $row) {
//            $domains[$row['NAME']] = $row['VALUE'];
            $domains[$row['VALUE']] = $row['NAME'];
        }
        return $domains;
    }

    public function get_user_file(){
        return file_get_contents($this->config->item('path_users_dat'));
    }

    public function save_user_file($data){
        $filename = $this->config->item('path_users_dat');
        $res = array();
        if(!file_exists($filename)){
            $res['code'] = 0;
            $res['msg'] = 'File not found';
        }elseif(!is_writable($filename)){
            $res['code'] = 0;
            $res['msg'] = 'File not writable';
        }else{
            $bt = file_put_contents($filename,$data);
            if($bt > 0 ){
                $res['code'] = 200;
                $res['msg'] = 'Written '.$bt.' bytes';
            }else{
                $res['code'] = 0;
                $res['msg'] = 'File not saved';
            }
        }
        return $res;
    }
    
    
    /*********  new function added in version 2.0 *****/////
    
    public function get_ul_style_display($node_obj) {
        return (array_key_exists($node_obj->get_hash(), $this->get_u_menu()) || $node_obj->is_root() ) ? 'block' : 'none';
    }

    public function get_ul_bookmark_style_display($node_obj) {
        return (array_key_exists($node_obj->get_hash(), $this->get_u_bookmarks()) || $node_obj->is_root() ) ? 'block' : 'none';
    }
    
}
